package com.nekonomics
package utils.magic

import com.nekonomics.utils.magic.TypeMagic.*

import scala.quoted.*

object SqlMagic {

  /**
   * generate sql like `insert into table_name (field_name) values (?)`.
   * it uses `?` placeholders for Update bind
   *
   */
  inline def insertSql[T]: String = ${ insertSqlImpl[T] }

  /**
   * generate sql like `insert into table_name (field_name) values `.
   * no placeholders.
   *
   */

  inline def insertSqlNoHolder[T]: String = ${ insertSqlNoHolderImpl[T] }

  /**
   *
   * @return like `(?[,?...])`
   */
  inline def fieldHolder4Sql[T]: String = ${ fieldHolder4SqlImpl[T]}

  /**
   * @return like`field_name as fieldName,...`
   */
  inline def fieldSnakeNames4Select[T]: String = ${ fieldSnakeNames4SelectImpl[T] }

  /**
   * @return like `field_name, ...`
   */
  inline def fieldSnakeNames4Sql[T]: String = ${ fieldSnakeNames4SqlImpl[T] }
  
  inline def fieldSnakeNames[T]: List[String] = ${ fieldSnakeNamesImpl[T] }

  inline def tableName4Sql[T]: String = ${ tableName4SqlImpl[T] }


  private def fieldSnakeNamesImpl[T: Type](using q: Quotes): Expr[List[String]] =
    Expr(getFieldNames[T].map(camelToSnake))

  private def tableName4SqlImpl[T: Type](using q: Quotes): Expr[String] = {
    val typeName = getTypeName[T]
    Expr("public." + toSnakeBaseName(typeName))
  }
  
  private def fieldHolder4SqlImpl[T: Type](using q: Quotes): Expr[String] = {
    val fields = getFieldNames[T]
    val placeholders = fields.map(_ => "?").mkString(", ")
    Expr(s"($placeholders)")
  }
  

  /**
   * generate sql like `insert into table_name (field_name) values (?)`.
   * it uses `?` placeholders for Update bind
   *
   */
  private def insertSqlImpl[T: Type](using q: Quotes): Expr[String] = {
    val tableStr = "public." + toSnakeBaseName(getTypeName[T])
    val fields = getFieldNames[T]
    val placeholders = List.fill(fields.size)("?").mkString(", ")
    val fieldList = fields.mkString(", ")
    Expr(s"INSERT INTO $tableStr ($fieldList) VALUES ($placeholders)")
  }

  private def insertSqlNoHolderImpl[T: Type](using q: Quotes): Expr[String] = {
    val tableStr = "public." + toSnakeBaseName(getTypeName[T])
    val fieldList = getFieldNames[T].map(camelToSnake).mkString(", ")
    Expr(s"INSERT INTO $tableStr ($fieldList) VALUES ")
  }

  
  private def fieldSnakeNames4SqlImpl[T: Type](using Quotes): Expr[String] = {
    Expr(getFieldNames[T].map(camelToSnake).mkString(", "))
  }
  

  private def fieldSnakeNames4SelectImpl[T: Type](using q: Quotes): Expr[String] = {
    val fieldNames = getFieldNames[T].map { orig =>
      val snake = camelToSnake(orig)
      s"$snake AS $orig"
    }
    Expr(fieldNames.mkString(", "))
  }


  def camelToSnake(name: String): String = {
    name.flatMap {
        case c if c.isUpper => "_" + c.toLower
        case c => c.toString
      }
      .stripPrefix("_")
  }

  private def toSnakeBaseName(typeName: String): String = {
    val base = typeName.replaceAll("DO$|Dto$|DTO$", "")
    camelToSnake(base)
  }


}
